﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Web.UI.HtmlControls;
using Helper;
using System.Text;
using System.Web.Services;


public partial class Upload : PageBase
{
    string uploadMode;
    string uploadFolder = "files/";
    ScriptManager currentScriptManager;

    protected void Page_Load(object sender, EventArgs e)
    {
        currentScriptManager = ScriptManager.GetCurrent(this.Page);
        uploadResultSummary.Visible = false;
        errorReportGridView.PagerSettings.FirstPageImageUrl=ResolveClientUrl("~/img/first-page.png");
        errorReportGridView.PagerSettings.FirstPageText = "First";
        errorReportGridView.PagerSettings.LastPageImageUrl = ResolveClientUrl("~/img/last-page.png");
        errorReportGridView.PagerSettings.LastPageText = "Last";

        errorReportGridView.PagerSettings.PreviousPageImageUrl = ResolveClientUrl("~/img/prev-page.png");
        errorReportGridView.PagerSettings.NextPageImageUrl = ResolveClientUrl("~/img/next-page.png");

        //ScriptManager.RegisterStartupScript(this, this.GetType(), "loading", "$('#uploadLoaderContainer').hide();",true);

        if (!this.IsPostBack)
        {
            /*Append page title*/
            Common.AppendPageTitle(Master, Globals.MasterPageHeaderID, " - Upload");

            /*add placer holder text to sheet text field*/
            txtSheetName.Attributes.Add("placeholder", "Sheet1");

            errorReportContainer.Attributes.CssStyle.Add(HtmlTextWriterStyle.Display, "none");

            uploadErrorReviewTitle.Visible = false;
            /*hide the upload review heading container*/
            uploadReviewHeader.Visible = false;

            //imgFileStructure.Visible = false;

            ResetErrorDataCache();
        }
        else
        {
            if (string.Compare(gridViewActionField.Value, "paging", true) == 0)
            {
                errorReportContainer.Attributes.CssStyle.Add(HtmlTextWriterStyle.Display, "block");
            }
            else
            {
                errorReportContainer.Attributes.CssStyle.Add(HtmlTextWriterStyle.Display, "none");
            }
        }

        /*check mode query string parmeter exists*/
        if (Request.QueryString["mode"] != null)
        {
            /*set the mode*/
            uploadMode = Request.QueryString["mode"].ToString().Trim();
        }
        else
        {
            /*set default*/
            uploadMode = "addon";
        }

        if (isAddonLoansUpload())
        {
            ltUploadContextLengend.Text = "Addon Loans Upload";
            /*assign the image location of the correct file structure that should be uplaoded*/
            imgFileStructure.ImageUrl = "~/img/Templates/AddonStructure.png";
            uploadFormContainer.Visible = true;
            imgFileStructure.Visible = true;
        }
        else if (isAddonClosedLoansUpload())
        {
            ltUploadContextLengend.Text = "Closed Addon Loans Upload";
            imgFileStructure.ImageUrl = "~/img/Templates/ClosedAddonStructure.png";
            /*display the image and the error list on screen*/
            imgFileStructure.Visible = true;
            uploadFormContainer.Visible = true;
        }
        else if (isAddonCancelledLoansUpload())
        {
            ltUploadContextLengend.Text = "Cancelled Addon Loans Upload";
            imgFileStructure.ImageUrl = "~/img/Templates/CancelledAddonStructure.png";
            /*display the image and the error list on screen*/
            imgFileStructure.Visible = true;
            uploadFormContainer.Visible = true;
        }
        else if (isReducingBalanceUpload())
        {
            ltUploadContextLengend.Text = "Reducing Balance Loans Upload";
            /*assign the image location of the correct file structure that should be uplaoded*/
            imgFileStructure.ImageUrl = "~/img/Templates/RedBalStructure.png";
            /*display the image and the error list on screen*/
            imgFileStructure.Visible = true;
            uploadFormContainer.Visible = true;
        }
        else if (isReducingBalanceClosedUpload())
        {
            ltUploadContextLengend.Text = "Closed Reducing Balance Loans Upload";
            imgFileStructure.ImageUrl = "~/img/Templates/ClosedRedBalStructure.png";
            /*display the image and the error list on screen*/
            imgFileStructure.Visible = true;
            uploadFormContainer.Visible = true;
        }
        else if (isReducingBalanceCancelledUpload())
        {
            ltUploadContextLengend.Text = "Cancelled Reducing Balance Loans Upload";
            imgFileStructure.ImageUrl = "~/img/Templates/CancelledRedBalStructure.png";
            /*display the image and the error list on screen*/
            imgFileStructure.Visible = true;
            uploadFormContainer.Visible = true;
        }
        else
        {
            ltUploadContextLengend.Text = "Loans Upload";
            uploadFormContainer.Visible = false;
        }
    }


    /// <summary>
    /// Checks if the mode of the page is Reducing Balance
    /// </summary>
    /// <returns>returns <c>true</c> if the mode of webpage is Reducing Balance. <c>false</c> if not.</returns>
    protected bool isReducingBalanceUpload()
    {
        if (string.Compare(uploadMode, "reducingbalance", true)==0)
        {
            return true;
        }
        return false;
    }

    protected bool isReducingBalanceClosedUpload()
    {
        if (string.Compare(uploadMode, "reducingbalanceclosed", true) == 0)
        {
            return true;
        }
        return false;
    }

    protected bool isReducingBalanceCancelledUpload()
    {
        if (string.Compare(uploadMode, "reducingbalancecancelled", true) == 0)
        {
            return true;
        }
        return false;
    }

    protected bool isReducingBalanceGeneralLoansUpload()
    {
        bool status = false;
        if (string.Compare(uploadMode, "reducingbalancecancelled", true) == 0)
        {
            status = true;
        }
        else if (string.Compare(uploadMode, "reducingbalanceclosed", true) == 0)
        {
            status = true;
        }
        else if (string.Compare(uploadMode, "reducingbalance", true) == 0)
        {
            status = true;
        }
        return status;
    }

    /// <summary>
    /// Checks if the mode of the page is Projected Interest
    /// </summary>
    /// <returns>returns <c>true</c> if the mode of webpage is Projected Interest. <c>false</c> if not.</returns>
    protected bool isAddonLoansUpload()
    {
        if (string.Compare(uploadMode, "addon", true) == 0)
        {
            return true;
        }
        return false;
    }

    protected bool isAddonClosedLoansUpload()
    {
        if (string.Compare(uploadMode, "addonclosed", true) == 0)
        {
            return true;
        }
        return false;
    }

    protected bool isAddonCancelledLoansUpload()
    {
        if (string.Compare(uploadMode, "addoncancelled", true) == 0)
        {
            return true;
        }
        return false;
    }

    protected bool isAddonGeneralLoansUpload()
    {
        bool status = false;
        if (string.Compare(uploadMode, "addoncancelled", true) == 0)
        {
            status = true;
        }
        else if (string.Compare(uploadMode, "addonclosed", true) == 0)
        {
            status = true;
        }
        else if (string.Compare(uploadMode, "addon", true) == 0)
        {
            status = true;
        }
        return status;
    }

    protected void btnCancel_Click(object sender, EventArgs e)
    {
        ResetErrorDataCache();
        Response.Redirect("~/Default.aspx", true);
    }

    /// <summary>
    /// Get the sheet name from the form textbox and appends format as required
    /// </summary>
    /// <returns>returns the sheet name of supplied in the textbox. If not sheet name was
    /// supplied then Sheet1$ (default) will returned for use</returns>
    protected string getSheetName()
    {
        if(string.IsNullOrEmpty(txtSheetName.Text.ToString().Trim()))
        {
            return "Sheet1$";
        }
        else 
            return ParseSheetName(txtSheetName.Text.ToString() + "$");
    }

    protected void ResetErrorDataCache()
    {
        if (Cache["errorList"] != null)
        {
            /*remove errorlist entry from cache*/
            Cache.Remove("errorList");
            /*reset gridview datasource and rebind to null*/
            errorReportGridView.DataSource = null;
            errorReportGridView.DataBind();           
            /*hide gridview*/
            uploadErrorReviewTitle.Visible = false;
            uploadReviewHeader.Visible = false;
           
        }
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        LoanBLL loans;  //business logic object        
        UploadResult uploadResult; //wrapper for summary of upload results

        /*hide message boxes*/
        errorMessage.Visible = false;
        infoMessage.Visible = false;
        warningMessage.Visible = false;
        successMessage.Visible = false;

        /*reset error cache to null*/
        ResetErrorDataCache();

        lblUploadStatus.InnerHtml = string.Empty;

        if (this.Page.IsValid)
        {
            //imgFileStructure.Visible = false;
            if (loanFileUploader.HasFile)
            {
                try
                {
                    /*check file type and ensure only excel files are uploaded*/
                    string fileExt = System.IO.Path.GetExtension(loanFileUploader.FileName);
                    string fileNameOnly = System.IO.Path.GetFileNameWithoutExtension(loanFileUploader.FileName);

                    if (fileExt == ".xls" || fileExt == ".xlsx")
                    {
                        DataSet dataSet = null;
                        
                        string filename = fileNameOnly + "_" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss") + fileExt;
                        loanFileUploader.SaveAs(Server.MapPath("files/") + filename);

                        if (isAddonLoansUpload())
                        {
                            if (WorkBookHasSheet(filename))
                            {
                                if (isAddonSheetColumnStructureValid(filename))
                                {
                                    dataSet = getLoanExcelSheetData(filename);

                                    if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables["loans"]!=null)
                                    {
                                        /*initialize business object that will handle the insert operation*/
                                        loans = new LoanBLL();

                                        /*call the insert method on the business object and assigned the return value to the upload summary object*/
                                        uploadResult = loans.InsertAddonLoans(dataSet.Tables[0]);
                                       
                                        /*display the status message for the insert operation*/
                                        successMessage.InnerHtml = "Loan upload operation completed. See summary below";
                                        successMessage.Visible = true;                                       

                                        /*add summary deatails to the summary table on page*/
                                        td_TotalRecords.InnerText = uploadResult.SummaryRecordsResult.TotalRecords.ToString();
                                        td_TotalRecordsValue.InnerText = uploadResult.SummaryRecordsResult.TotalRecordsValue.ToString("C");

                                        td_InsertedRecords.InnerText = uploadResult.SummaryRecordsResult.InsertedRecords.ToString();
                                        td_InsertedRecordsValue.InnerText = uploadResult.SummaryRecordsResult.InsertedRecordsValue.ToString("C");

                                        td_InvalidRecords.InnerText = uploadResult.SummaryRecordsResult.InvalidRecords.ToString();


                                        td_DuplicateRecords.InnerText = uploadResult.SummaryRecordsResult.DuplicateRecords.ToString();
                                        td_DuplicateRecordsValue.InnerText = uploadResult.SummaryRecordsResult.DuplicateRecordsValue.ToString("C");

                                        tbl_LoanCancellationResult.Visible = false;
                                        tbl_LoanClosedResult.Visible = false;
                                        tbl_LoanInsertUploadResult.Visible = true;

                                        /*display summary table*/
                                        uploadResultSummary.Visible = true;

                                        /*hide upload image template*/
                                        imgFileStructure.Visible = false;

                                        /*check if any error occured during the upload and display it to the user*/
                                        if (uploadResult.Log.Errors.Count > 0)
                                        {
                                            //int logId = uploadResult.Log.Save();
                                            errorReportGridView.DataSource = uploadResult.Log.Errors;
                                            errorReportGridView.DataBind();

                                            Cache["errorList"] = uploadResult.Log.Errors;

                                            uploadErrorReviewTitle.Visible = true;
                                            uploadReviewHeader.Visible = true;
                                        }
                                    }
                                }
                                else
                                {
                                    /*display error message notifying user of invalid file structue*/
                                    errorMessage.InnerHtml = "Unable to process worksheet: The structue is invalid for Addon Loans. <br />" +
                                       "Please see image below of correct worksheet structure. Modify worksheet structue and try again<br />";

                                    /*assign the image location of the correct file structure that should be uplaoded*/
                                    imgFileStructure.ImageUrl = "~/img/Templates/AddonStructure.png";
                                    /*display the image and the error list on screen*/
                                    imgFileStructure.Visible = true;
                                    errorMessage.Visible = true;
                                }
                            }
                            else
                            {
                                /*notify user that the sheet name is invalid*/
                                errorMessage.InnerHtml = "The Work book does not contain the specified sheet." +
                                     "<br />Please open the Excel file and ensure the sheetname you entered above corresponds with the sheetname in the file";
                                errorMessage.Visible = true;
                            }
                        }
                        else if (isAddonClosedLoansUpload())
                        {
                            if (WorkBookHasSheet(filename))
                            {
                                if (isClosedAddonSheetColumnStructureValid(filename))
                                {
                                    dataSet = getLoanExcelSheetData(filename);

                                    if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables["loans"] != null)
                                    {
                                        /*initialize business object that will handle the insert operation*/
                                        loans = new LoanBLL();

                                        /*call the insert method on the business object and assigned the return value to the upload summary object*/
                                        uploadResult = loans.InsertClosedAddonLoans(dataSet.Tables[0]);

                                        /*display the status message for the insert operation*/
                                        successMessage.InnerHtml = "Loan upload operation completed. See summary below";
                                        successMessage.Visible = true;

                                        /*add summary deatails to the summary table on page*/
                                        td_ClosedTotalRecords.InnerText = uploadResult.SummaryRecordsResult.TotalRecords.ToString();
                                        td_ClosedLoans.InnerText = uploadResult.SummaryRecordsResult.UpdatedRecords.ToString();
                                        td_ClosedUnknownLoans.InnerText = uploadResult.SummaryRecordsResult.UnknownRecords.ToString();
                                        td_ClosedInvalidRecords.InnerText = uploadResult.SummaryRecordsResult.InvalidRecords.ToString();

                                        tbl_LoanCancellationResult.Visible = false;
                                        tbl_LoanClosedResult.Visible = true;
                                        tbl_LoanInsertUploadResult.Visible = false;

                                        /*display summary table*/
                                        uploadResultSummary.Visible = true;

                                        /*hide upload image template*/
                                        imgFileStructure.Visible = false;

                                        /*check if any error occured during the upload and display it to the user*/
                                        if (uploadResult.Log.Errors.Count > 0)
                                        {
                                            //int logId = uploadResult.Log.Save();
                                            errorReportGridView.DataSource = uploadResult.Log.Errors;
                                            errorReportGridView.DataBind();

                                            Cache["errorList"] = uploadResult.Log.Errors;

                                            uploadErrorReviewTitle.Visible = true;
                                            uploadReviewHeader.Visible = true;
                                        }
                                    }
                                }
                                else
                                {
                                    /*display error message notifying user of invalid file structue*/
                                    errorMessage.InnerHtml = "Unable to process worksheet: The structue is invalid for Addon Loans. <br />" +
                                       "Please see image below of correct worksheet structure. Modify worksheet structue and try again<br />";

                                    /*assign the image location of the correct file structure that should be uplaoded*/
                                    imgFileStructure.ImageUrl = "~/img/Templates/ClosedAddonStructure.png";
                                    /*display the image and the error list on screen*/
                                    imgFileStructure.Visible = true;
                                    errorMessage.Visible = true;
                                }
                            }
                            else
                            {
                                /*notify user that the sheet name is invalid*/
                                errorMessage.InnerHtml = "The Work book does not contain the specified sheet." +
                                     "<br />Please open the Excel file and ensure the sheetname you entered above corresponds with the sheetname in the file";
                                errorMessage.Visible = true;
                            }
                        }
                        else if (isAddonCancelledLoansUpload())
                        {
                            if (WorkBookHasSheet(filename))
                            {
                                if (isCancelledAddonSheetColumnStructureValid(filename))
                                {
                                    dataSet = getLoanExcelSheetData(filename);

                                    if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables["loans"] != null)
                                    {
                                        /*initialize business object that will handle the insert operation*/
                                        loans = new LoanBLL();

                                        /*call the insert method on the business object and assigned the return value to the upload summary object*/
                                        uploadResult = loans.RemoveCancelledAddonLoans(dataSet.Tables[0]);

                                        /*display the status message for the insert operation*/
                                        successMessage.InnerHtml = "Loan upload operation completed. See summary below";
                                        successMessage.Visible = true;

                                        /*add summary deatails to the summary table on page*/
                                        td_TotalCancelledRecords.InnerText = uploadResult.SummaryRecordsResult.TotalRecords.ToString();
                                        td_CancelledLoans.InnerText = uploadResult.SummaryRecordsResult.RemovedRecords.ToString();
                                        td_CancelledInvalidRecords.InnerText = uploadResult.SummaryRecordsResult.InvalidRecords.ToString();
                                        td_CancelledUnknownLoans.InnerText = uploadResult.SummaryRecordsResult.UnknownRecords.ToString();

                                        tbl_LoanCancellationResult.Visible = true;
                                        tbl_LoanClosedResult.Visible = false;
                                        tbl_LoanInsertUploadResult.Visible = false;

                                        /*display summary table*/
                                        uploadResultSummary.Visible = true;

                                        /*hide upload image template*/
                                        imgFileStructure.Visible = false;

                                        /*check if any error occured during the upload and display it to the user*/
                                        if (uploadResult.Log.Errors.Count > 0)
                                        {
                                            //int logId = uploadResult.Log.Save();
                                            errorReportGridView.DataSource = uploadResult.Log.Errors;
                                            errorReportGridView.DataBind();

                                            Cache["errorList"] = uploadResult.Log.Errors;

                                            uploadErrorReviewTitle.Visible = true;
                                            uploadReviewHeader.Visible = true;
                                        }
                                    }
                                }
                                else
                                {
                                    /*display error message notifying user of invalid file structue*/
                                    errorMessage.InnerHtml = "Unable to process worksheet: The structue is invalid for Addon Loans. <br />" +
                                       "Please see image below of correct worksheet structure. Modify worksheet structue and try again<br />";

                                    /*assign the image location of the correct file structure that should be uplaoded*/
                                    imgFileStructure.ImageUrl = "~/img/Templates/CancelledAddonStructure.png";
                                    /*display the image and the error list on screen*/
                                    imgFileStructure.Visible = true;
                                    errorMessage.Visible = true;
                                }
                            }
                            else
                            {
                                /*notify user that the sheet name is invalid*/
                                errorMessage.InnerHtml = "The Work book does not contain the specified sheet." +
                                     "<br />Please open the Excel file and ensure the sheetname you entered above corresponds with the sheetname in the file";
                                errorMessage.Visible = true;
                            }
                        }
                        else if (isReducingBalanceUpload())
                        {
                            if (WorkBookHasSheet(filename))
                            {
                                if (isReducingBalanceColumnStructureValid(filename))
                                {
                                    dataSet = getLoanExcelSheetData(filename);
                                    
                                    if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables["loans"] != null)
                                    {
                                        /*initialize business object that will handle the insert operation*/
                                        loans = new LoanBLL();

                                        /*call the insert method on the business object and assigned the return value to the upload summary object*/
                                        uploadResult = loans.InsertReducingBalanceLoans(dataSet.Tables[0]);

                                        /*display the status message for the insert operation*/
                                        successMessage.InnerHtml = "Loan upload operation completed. See summary below";
                                        successMessage.Visible = true; 
                                        
                                        /*add summary deatails to the summary table on page*/
                                        td_TotalRecords.InnerText = uploadResult.SummaryRecordsResult.TotalRecords.ToString();
                                        td_TotalRecordsValue.InnerText = uploadResult.SummaryRecordsResult.TotalRecordsValue.ToString("C");

                                        td_InsertedRecords.InnerText = uploadResult.SummaryRecordsResult.InsertedRecords.ToString();
                                        td_InsertedRecordsValue.InnerText = uploadResult.SummaryRecordsResult.InsertedRecordsValue.ToString("C");

                                        td_InvalidRecords.InnerText = uploadResult.SummaryRecordsResult.InvalidRecords.ToString();

                                        td_DuplicateRecords.InnerText = uploadResult.SummaryRecordsResult.DuplicateRecords.ToString();
                                        td_DuplicateRecordsValue.InnerText = uploadResult.SummaryRecordsResult.DuplicateRecordsValue.ToString("C");

                                        tbl_LoanCancellationResult.Visible = false;
                                        tbl_LoanClosedResult.Visible = false;
                                        tbl_LoanInsertUploadResult.Visible = true;

                                        /*display summary table*/
                                        uploadResultSummary.Visible = true;

                                        /*hide upload image template*/
                                        imgFileStructure.Visible = false;

                                        /*check if any error occured during the upload and display it to the user*/
                                        if (uploadResult.Log.Errors.Count > 0)
                                        {
                                            //int logId = uploadResult.Log.Save();
                                            errorReportGridView.DataSource = uploadResult.Log.Errors;
                                            errorReportGridView.DataBind();

                                            Cache["errorList"] = uploadResult.Log.Errors;

                                            uploadErrorReviewTitle.Visible = true;
                                            uploadReviewHeader.Visible = true;
                                        }
                                    }
                                }
                                else
                                {
                                    /*display error message notifying user of invalid file structue*/
                                    errorMessage.InnerHtml = "Unable to process worksheet: The structue is invalid for Reducing Balance Loans. <br />" +
                                        "Please see image below of correct worksheet structure. Modify worksheet structue and try again<br />";
                                   
                                    /*assign the image location of the correct file structure that should be uplaoded*/
                                    imgFileStructure.ImageUrl = "~/img/Templates/RedBalStructure.png";
                                    /*display the image and the error list on screen*/
                                    imgFileStructure.Visible = true;
                                    errorMessage.Visible = true;
                                }
                            }
                            else
                            {
                                /*notify user that the sheet name is invalid*/
                                errorMessage.InnerHtml = "The Work book does not contain the specified sheet." +
                                     "<br />Please open the Excel file and ensure the sheetname you entered above corresponds with the sheetname in the file";
                                errorMessage.Visible = true;
                            }
                        }
                        else if (isReducingBalanceClosedUpload())
                        {
                            if (WorkBookHasSheet(filename))
                            {
                                if (isClosedReducingBalanceSheetColumnStructureValid(filename))
                                {
                                    dataSet = getLoanExcelSheetData(filename);

                                    if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables["loans"] != null)
                                    {
                                        /*initialize business object that will handle the insert operation*/
                                        loans = new LoanBLL();

                                        /*call the insert method on the business object and assigned the return value to the upload summary object*/
                                        uploadResult = loans.InsertClosedReducingBalanceLoans(dataSet.Tables[0]);

                                        /*display the status message for the insert operation*/
                                        successMessage.InnerHtml = "Loan upload operation completed. See summary below";
                                        successMessage.Visible = true;

                                        /*add summary deatails to the summary table on page*/
                                        td_ClosedTotalRecords.InnerText = uploadResult.SummaryRecordsResult.TotalRecords.ToString();
                                        td_ClosedLoans.InnerText = uploadResult.SummaryRecordsResult.UpdatedRecords.ToString();
                                        td_ClosedUnknownLoans.InnerText = uploadResult.SummaryRecordsResult.UnknownRecords.ToString();
                                        td_ClosedInvalidRecords.InnerText = uploadResult.SummaryRecordsResult.InvalidRecords.ToString();

                                        tbl_LoanCancellationResult.Visible = false;
                                        tbl_LoanClosedResult.Visible = true;
                                        tbl_LoanInsertUploadResult.Visible = false;

                                        /*display summary table*/
                                        uploadResultSummary.Visible = true;

                                        /*hide upload image template*/
                                        imgFileStructure.Visible = false;

                                        /*check if any error occured during the upload and display it to the user*/
                                        if (uploadResult.Log.Errors.Count > 0)
                                        {
                                            //int logId = uploadResult.Log.Save();
                                            errorReportGridView.DataSource = uploadResult.Log.Errors;
                                            errorReportGridView.DataBind();

                                            Cache["errorList"] = uploadResult.Log.Errors;

                                            uploadErrorReviewTitle.Visible = true;
                                            uploadReviewHeader.Visible = true;
                                        }
                                    }
                                }
                                else
                                {
                                    /*display error message notifying user of invalid file structue*/
                                    errorMessage.InnerHtml = "Unable to process worksheet: The structue is invalid for Reducing Balance Loans. <br />" +
                                        "Please see image below of correct worksheet structure. Modify worksheet structue and try again<br />";

                                    /*assign the image location of the correct file structure that should be uplaoded*/
                                    imgFileStructure.ImageUrl = "~/img/Templates/ClosedRedBalStructure.png";
                                    /*display the image and the error list on screen*/
                                    imgFileStructure.Visible = true;
                                    errorMessage.Visible = true;
                                }
                            }
                            else
                            {
                                /*notify user that the sheet name is invalid*/
                                errorMessage.InnerHtml = "The Work book does not contain the specified sheet." +
                                     "<br />Please open the Excel file and ensure the sheetname you entered above corresponds with the sheetname in the file";
                                errorMessage.Visible = true;
                            }
                        }
                        else if (isReducingBalanceCancelledUpload())
                        {
                            if (WorkBookHasSheet(filename))
                            {
                                if (isCancelledReducingBalanceSheetColumnStructureValid(filename))
                                {
                                    dataSet = getLoanExcelSheetData(filename);

                                    if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables["loans"] != null)
                                    {
                                        /*initialize business object that will handle the insert operation*/
                                        loans = new LoanBLL();

                                        /*call the insert method on the business object and assigned the return value to the upload summary object*/
                                        uploadResult = loans.RemoveCancelledReducingBalanceLoans(dataSet.Tables[0]);

                                        /*display the status message for the insert operation*/
                                        successMessage.InnerHtml = "Loan upload operation completed. See summary below";
                                        successMessage.Visible = true;

                                        /*add summary deatails to the summary table on page*/
                                        td_TotalCancelledRecords.InnerText = uploadResult.SummaryRecordsResult.TotalRecords.ToString();
                                        td_CancelledLoans.InnerText = uploadResult.SummaryRecordsResult.RemovedRecords.ToString();
                                        td_CancelledInvalidRecords.InnerText = uploadResult.SummaryRecordsResult.InvalidRecords.ToString();
                                        td_CancelledUnknownLoans.InnerText = uploadResult.SummaryRecordsResult.UnknownRecords.ToString();

                                        tbl_LoanCancellationResult.Visible = true;
                                        tbl_LoanClosedResult.Visible = false;
                                        tbl_LoanInsertUploadResult.Visible = false;

                                        /*display summary table*/
                                        uploadResultSummary.Visible = true;

                                        /*hide upload image template*/
                                        imgFileStructure.Visible = false;

                                        /*check if any error occured during the upload and display it to the user*/
                                        if (uploadResult.Log.Errors.Count > 0)
                                        {
                                            //int logId = uploadResult.Log.Save();
                                            errorReportGridView.DataSource = uploadResult.Log.Errors;
                                            errorReportGridView.DataBind();

                                            Cache["errorList"] = uploadResult.Log.Errors;

                                            uploadErrorReviewTitle.Visible = true;
                                            uploadReviewHeader.Visible = true;
                                        }
                                    }
                                }
                                else
                                {
                                    /*display error message notifying user of invalid file structue*/
                                    errorMessage.InnerHtml = "Unable to process worksheet: The structue is invalid for Addon Loans. <br />" +
                                       "Please see image below of correct worksheet structure. Modify worksheet structue and try again<br />";

                                    /*assign the image location of the correct file structure that should be uplaoded*/
                                    imgFileStructure.ImageUrl = "~/img/Templates/CancelledAddonStructure.png";
                                    /*display the image and the error list on screen*/
                                    imgFileStructure.Visible = true;
                                    errorMessage.Visible = true;
                                }
                            }
                        }
                        else
                        {
                            /*notify the user that they must select an upload type*/
                            infoMessage.InnerHtml = "An upload type was not choosen. Please Choose Reducing Balance or Projected Interest from the menu above.";
                            infoMessage.Visible = true;
                        }
                    }


                    else
                    {
                        /*notify the user that the file type is invalid, only excel files are allowed*/
                        errorMessage.InnerHtml = "Only Microsoft Excel files are allowed";
                        errorMessage.Visible = true;
                    }
                }
                catch (Exception ex)
                {

                    Error exception = new Error(ex.GetType().ToString(), ex.Message, DateTime.Now, stackTrace: ex.StackTrace, additionalDetails: ex.Source);
                    ErrorLogBLL exceptionUtility = new ErrorLogBLL();
                    int logId = exceptionUtility.LogException(exception);

                    errorMessage.InnerHtml = "An error occurred during file upload.<br />" +
                        "The error has been logged to the system with logId:" + logId + ".<br />" +
                        "Please notify the site administrator for assistance.";
                    errorMessage.Visible = true;
                    
                }
            }
            else
            {
                /*notify the user to select a file*/
                infoMessage.InnerHtml = "No file has been selected";
                infoMessage.Visible = true;
            }
        }
        else
        {
            /*page is invalid - notify the user*/
            errorMessage.InnerHtml = "There are errors on the page. Please correct the errors and try again." +
                "<br />Contact the site administrator if you require assistance";
            errorMessage.Visible = true;
        }
    }


    protected bool WorkBookHasSheet(string filePath)
    {
        string sheetName = getSheetName();
        OleDbConnectionProvider oledbConnProvider;

     
        bool result = false;
        try
        {
            oledbConnProvider = new OleDbConnectionProvider(Server.MapPath(uploadFolder + filePath));

            result = oledbConnProvider.WorkBookHasSheet(sheetName);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return result;
    }

    /// <summary>
    /// Gets all the sheet data of a specied worksheet of a specied excel workbook file
    /// </summary>
    /// <param name="filePath">The path to the excel workbook file</param>
    /// <returns>returns a <c>DataSet</c> containing the specified table (worksheet) that was read.</returns>
    protected DataSet getLoanExcelSheetData(string filePath)
    {
        string sheetName = getSheetName();
        OleDbConnectionProvider oledbConnProvider;
                   
        DataSet ds = null;

        try
        {
            oledbConnProvider = new OleDbConnectionProvider(Server.MapPath(uploadFolder + filePath), "SELECT * FROM [" + sheetName + "]");

            ds = oledbConnProvider.GetDataSetWithSingleTable("Loans");
        }
        catch(Exception ex)
        {
            throw ex;
        }
        return ds;
    }

    /// <summary>
    /// Checks if the column structure of the Projected Interest worksheet is correct
    /// </summary>
    /// <param name="filePath">Path to the excel workbook file</param>
    /// <returns>returns <c>true</c> if the structure is correct. <c>false</c> if not.</returns>
    bool isAddonSheetColumnStructureValid(string filePath)
    {
        string sheetName = getSheetName();
        OleDbConnectionProvider oledbConnProvider;

        DataSet ds = null;

        try
        {
            oledbConnProvider = new OleDbConnectionProvider(Server.MapPath(uploadFolder + filePath), "SELECT * FROM [" + sheetName + "]");

            ds = oledbConnProvider.GetDataSetWithSingleTable("Loans");

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables["Loans"];

                if (dt.Columns.Count >= 7 && !dt.Columns[6].Caption.Contains("F7"))//if (dt.Columns.Count == 7 && !dt.Columns[6].Caption.Contains("F7"))
                {
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return false;
    }

    bool isClosedAddonSheetColumnStructureValid(string filePath)
    {
        string sheetName = getSheetName();
        OleDbConnectionProvider oledbConnProvider;

        DataSet ds = null;

        try
        {
            oledbConnProvider = new OleDbConnectionProvider(Server.MapPath(uploadFolder + filePath), "SELECT * FROM [" + sheetName + "]");

            ds = oledbConnProvider.GetDataSetWithSingleTable("Loans");

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables["Loans"];

                if (dt.Columns.Count >= 2 && !dt.Columns[1].Caption.Contains("F7"))//if (dt.Columns.Count == 7 && !dt.Columns[6].Caption.Contains("F7"))
                {
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return false;
    }

    bool isCancelledAddonSheetColumnStructureValid(string filePath)
    {
        string sheetName = getSheetName();
        OleDbConnectionProvider oledbConnProvider;

        DataSet ds = null;

        try
        {
            oledbConnProvider = new OleDbConnectionProvider(Server.MapPath(uploadFolder + filePath), "SELECT * FROM [" + sheetName + "]");

            ds = oledbConnProvider.GetDataSetWithSingleTable("Loans");

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables["Loans"];

                if (dt.Columns.Count >= 1 && !dt.Columns[0].Caption.Contains("F7"))//if (dt.Columns.Count == 7 && !dt.Columns[6].Caption.Contains("F7"))
                {
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return false;
    }


    /// <summary>
    /// Checks if the column structure of the Reducing Balance worksheet is correct
    /// </summary>
    /// <param name="filePath">Path to the excel workbook file</param>
    /// <returns>returns <c>true</c> if the structure is correct. <c>false</c> if not.</returns>
    bool isReducingBalanceColumnStructureValid(string filePath)
    {
        string sheetName = getSheetName();
        OleDbConnectionProvider oledbConnProvider;

        DataSet ds = null;

        try
        {
            oledbConnProvider = new OleDbConnectionProvider(Server.MapPath(uploadFolder + filePath), "SELECT * FROM [" + sheetName + "]");

            ds = oledbConnProvider.GetDataSetWithSingleTable("Loans");

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables["Loans"];

                if (dt.Columns.Count >= 7 && !dt.Columns[6].Caption.Contains("F7"))
                {
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return false;
    }

    bool isClosedReducingBalanceSheetColumnStructureValid(string filePath)
    {
        string sheetName = getSheetName();
        OleDbConnectionProvider oledbConnProvider;

        DataSet ds = null;

        try
        {
            oledbConnProvider = new OleDbConnectionProvider(Server.MapPath(uploadFolder + filePath), "SELECT * FROM [" + sheetName + "]");

            ds = oledbConnProvider.GetDataSetWithSingleTable("Loans");

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables["Loans"];

                if (dt.Columns.Count >= 2 && !dt.Columns[1].Caption.Contains("F7"))//if (dt.Columns.Count == 7 && !dt.Columns[6].Caption.Contains("F7"))
                {
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return false;
    }

    bool isCancelledReducingBalanceSheetColumnStructureValid(string filePath)
    {
        string sheetName = getSheetName();
        OleDbConnectionProvider oledbConnProvider;

        DataSet ds = null;

        try
        {
            oledbConnProvider = new OleDbConnectionProvider(Server.MapPath(uploadFolder + filePath), "SELECT * FROM [" + sheetName + "]");

            ds = oledbConnProvider.GetDataSetWithSingleTable("Loans");

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables["Loans"];

                if (dt.Columns.Count >= 1 && !dt.Columns[0].Caption.Contains("F7"))//if (dt.Columns.Count == 7 && !dt.Columns[6].Caption.Contains("F7"))
                {
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return false;
    }

    /// <summary>
    /// Parses the supplied sheetname to ensure that is has the correct format
    /// </summary>
    /// <param name="sheetName">specified sheet name</param>
    /// <returns>Returns the correctly parsed sheet name</returns>
    private string ParseSheetName(string sheetName)
    {
        if (sheetName.Contains(" "))
            return "'" + sheetName + "'";
        else
            return sheetName;
    }

    protected void ErrorReportGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gridViewActionField.Value = "paging";
        errorReportContainer.Attributes.CssStyle.Add(HtmlTextWriterStyle.Display, "block");

        if (Cache["errorList"] != null)
        {
            errorReportGridView.DataSource = Cache["errorList"] as List<Error>;
        }
        errorReportGridView.PageIndex = e.NewPageIndex;
        errorReportGridView.DataBind();
    }

    protected void ErrorReportGridView_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        Error uploadErrorItem = e.Row.DataItem as Error;
        Loan errorLoan;

        if (uploadErrorItem != null)
        {
            
            if (isReducingBalanceUpload())
            {
                errorLoan = XMLWrite.Deserialize<ReducingBalanceLoan>(uploadErrorItem.AdditionalDetails) as ReducingBalanceLoan;
            }
            else
            {
                errorLoan = XMLWrite.Deserialize<Loan>(uploadErrorItem.AdditionalDetails) as Loan;
            }


            if (errorLoan != null)
            {
                HtmlGenericControl recordSpan = e.Row.FindControl("recordSpan") as HtmlGenericControl;
                if (recordSpan != null)
                {
                    if (errorLoan != null)
                    recordSpan.InnerText = uploadErrorItem.RecordNo.ToString();
                }

                HtmlGenericControl loanNoSpan = e.Row.FindControl("loanNoSpan") as HtmlGenericControl;
                if (loanNoSpan != null)
                {
                    if (errorLoan != null)
                        loanNoSpan.InnerText = errorLoan.LoanNo.ToString();
                }
                HtmlGenericControl errorMsgSpan = e.Row.FindControl("errorMsgSpan") as HtmlGenericControl;
                if (errorMsgSpan != null)
                {
                    if (errorMsgSpan != null)
                        errorMsgSpan.InnerText = uploadErrorItem.Message.ToString();
                }
            }
        }
        
    }
}